The Connection Object

The ADO Connection object represents an open connection to a data source. This data source might be a database, an ODBC source, or any other source for which an OLE DB provider exists. The Connection object lets you specify all the necessary parameters—for example, the server and the database names, the user's name and password, and the timeout—before opening the data source. Connection objects are also important because they serve as containers for transactions. Each connection belongs to a given client application and is closed only when you explicitly close it, when you set the object variable to Nothing, or when your application ends.

The Connection object doesn't expose many properties. Rather than list each property individually, I've grouped them according to their purposes: Preparing for the connection, managing transactions, and determining the state of the connection and what version of ADO is running.

A group of properties lets you specify which database should be open and in which mode. All these properties can be written to before the connection is opened, but they become read-only after the connection has been established. The Provider property is the name of the OLE DB provider for the connection—for example, "SQLOLEDB" for the Microsoft SQL Server OLE DB Provider. If you leave this property unassigned, the default MSDASQL provider is used which is the OLE DB Provider for ODBC Drivers, a sort of bridge that permits you to connect to almost any relational database on earth even if an OLE DB provider hasn't been developed for it yet (provided that an ODBC driver exists for that database, of course). MSDASQL is also known by the code name "Kagera."

Instead of assigning a value to the Provider property, you can pass the provider's name in the ConnectionString property together with other parameters the OLE DB provider expects. For example, this connection string opens the Biblio.mdb database:

Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" _ & "Data Source=C:\Microsoft Visual Studio\Vb98\Biblio.mdb"

The following connection string opens the Pubs SQL Server database on a server named "ServerNT". (The connection string also contains the user's name and password.)

cn.ConnectionString = "Provider=SQLOLEDB;Server=ServerNT;" _ & "User ID=MyID;Password=MyPWD;Data Source=Pubs"

You shouldn't specify the provider name in the ConnectionString property and the Provider property at the same time because the results are unpredictable.

A simple way to build a connection string is to drop an ADO Data control on a form, open its property pages, select the Use Connection String option, and click on the Build button. A new dialog box appears, in which you can select the provider, the user name, the user password, and all the provider-dependent dynamic properties, as shown in Figure 13-3. When you've finished, the complete string connection appears in the General tab of the Properties dialog box.

Mastering the syntax for specifying the ConnectionString property can be difficult because the string can include many different arguments in the form argname=value. This task is made more complex by the fact that when you're connecting to an ODBC source, the ConnectionString property also supports ODBC attributes that can coexist with newer OLE DB attributes. Table 13-1 lists a few of the most common attributes that you can specify in this string.

Table 13-1. Some of the arguments you can use in the ConnectionString property.

Argument	Description
Data Source	The name of the SQL Server or the name of the MDB database to which you want to connect. When connecting to an ODBC source, this argument can also be the name of a Data Source Name (DSN).
DSN	An ODBC source name registered on the current machine; this argument can replace the Data Source argument.
Filename	A file that contains information about the connection; this argument can be an ODBC DSN file or a Microsoft Data Link (UDL) file.
Initial Catalog	The name of the default database. When connecting to an ODBC source, you can also use the Database argument.
Password	The user's password. When connecting to an ODBC source, you can use the PWD argument. You don't need to pass your user ID and password if you're connecting to SQL Server and you use integrated security.
Persist Security Info	True if ADO stores the user ID and the password in the data link.
Provider	The name of the OLE DB provider; the default value is MSDASQL, the provider for ODBC sources.
User ID	The user's name. When connecting to an ODBC source, you can use the UID argument instead.

The DefaultDatabase property is the name of the default database for the connection. Many ADO dialog boxes refer to this property with the name Initial Catalog. This property isn't available until the connection has been opened, and it's read-only afterward.

The Connection object exposes two properties that let you tailor your application to the speed of the network and the database server. The ConnectionTimeout property specifies the number of seconds that ADO will wait before raising a timeout error when trying to establish a connection. (The default value is 15 seconds.) The CommandTimeout property specifies the number of seconds that ADO waits for a database command or a query to complete. (The default is 30 seconds.) This value is used for all the queries performed on the Connection object only; it isn't inherited by a Command object that uses the same connection. (A Command object is affected only by its own CommandTimeout property.)

Figure 13-3. You can interactively create a connection string using the custom property pages of an ADO Data control. The All page gathers all the custom properties exposed by the selected OLE DB provider (SQLOLEDB in this case).

The CursorLocation property specifies whether a cursor is to be created, and if so, where. The possible values for this property are 2-adUseServer for server-side or driver-supplied cursors and 3-adUseClient for client-side cursors, including dissociated Recordsets. (Dissociated Recordsets are those that aren't associated with an active Connection object.)

The Mode property indicates the permissions on the connection. It can take a value from the following list:

Value	Description
1-adModeRead	Allows read-only data access
2-adModeWrite	Allows write-only data access
3-adModeReadWrite	Allows both read and write data access
4-adModeShareDenyRead	Prevents other clients from opening a connection with read permissions
8-adModeShareDenyWrite	Prevents other clients from opening a connection with write permissions
12-adModeShareExclusive	Prevents other clients from opening a connection to the same data source
16-adModeShareDenyNone	Allows other clients to open a connection with any permissions

If you don't assign a value to this property, it defaults to 0-adModeUnknown. You can write to this property only when the connection is closed; on an open connection, this property is read-only.

The IsolationLevel property affects how transactions are executed in the connection. This is a bit-field property that can be the sum of one or more of the following values:

Value	Description
&H10-adXactChaos	You can't overwrite pending changes from more highly isolated transactions.
&H100-adXactBrowse	You can view changes in other transactions even before they are committed.
&H1000-adXactCursorStability	You can't view changes in other transactions until they have been committed.
&H10000-adXactRepeatableRead	You can't see changes in other transactions, but you will see them if you repeat the query.
&H100000-adXactIsolated	Transactions are isolated from other transactions.
-1-adXactUnspecified	The isolation level can't be determined.

The IsolationLevel property is read/write, but any change you make to its value will take effect only when the next BeginTrans method is issued. Providers don't necessarily support all the isolation levels listed previously: If you request an unsupported isolation level, the provider usually enforces the next greater level.

The Attributes property contains two bits that affect what happens when a transaction is committed or rolled back. The bit &H20000-adXactCommitRetaining automatically starts a new transaction after every CommitTrans method, and the bit &H40000-adXactAbortRetaining starts a new transaction after every RollbackTrans method. Not all providers allow you to automatically start a new transaction after every CommitTrans and RollbackTrans method, however.

The State property is a read-only, bit-field property that reports the current state of the connection. It can be the sum of one or more of the following values:

Value	Description
0-adStateClosed	The connection is closed.
1-adStateOpen	The connection is open.
2-adStateConnecting	The connection is being opened.
4-adStateExecuting	The connection is executing a command.
8-adStateFetching	A Recordset is being retrieved.

You should query this property when you aren't sure about a Connection object's state because when the object is closed or is fetching data, many of its properties aren't usable.

The last property of the Connection object, Version, returns a read-only string that identifies the version of ADO in use. For example, under ADO 2.0, this property returns "2.0."

The methods of the Connection object let you do four things: open a connection, run a command, manage transactions on the active connection, and determine the structure of the database tables.

The most important method of the Connection object is Open, which establishes the connection. Its syntax is as follows:

Open [ConnectionString], [UserID], [Password], [Options]

The first argument has the same meaning as the ConnectionString property, UserID is the user's login name, and Password is the user's password. If Options is set to 16-adAsyncConnect, the connection is opened in asynchronous mode and it won't be available until the ConnectComplete event fires. All arguments are optional, but if you pass the UserID and Password arguments, you shouldn't specify them also in the ConnectionString argument or property. For example, the following statement opens an asynchronous connection to the Pubs database of SQL Server and specifies the "sa" user name with a blank password:

Dim cn As New ADODB.Connection cn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=Pubs;" _ & "User ID=sa;Password=;", , , adAsyncConnect

You close an open connection using the Close method, which takes no arguments.

The Execute method performs an action query or a select query on the connection. The syntax of this method depends on the type of the action; if you're executing an action that doesn't return a Recordset (for example, an INSERT, UPDATE, or DELETE SQL statement), this is the correct syntax:

Execute CommandText, [RecordsAffected], [Options]

CommandText is the name of a stored procedure, a table, or the text of an SQL query. RecordsAffected is a Long variable that receives the number of records that have been affected by the command. Options is an enumerated value that indicates how the string in CommandText should be interpreted and can be one of the following constants:

Value	Description
1-adCmdText	The text of an SQL query
2-adCmdTable	A database table
4-adCmdStoredProc	A stored procedure
8-adCmdUnknown	Unspecified; the provider will determine the correct type
512-adCmdTableDirect	A database table that should be opened directly (an operation that you should avoid on SQL Server databases)

If you pass the value adCmdUnknown or omit the Options argument, the OLE DB provider is usually able to find out the type of the operation, but at the expense of some overhead. For this reason, you should always pass a correct value in this argument.

If you're executing a command that returns a Recordset, the syntax of the Execute method is slightly different:

Execute(CommandText, [RecordsAffected], [Options]) As Recordset

You should assign the result of this method to a Recordset object so that you can later browse the results. The Execute command can create Recordset objects with default settings only—that is, read-only, forward-only Recordsets with the cache size equal to 1.

You can perform asynchronous commands by adding the 16-adAsyncExecute constant to the Options argument. You might also decide to populate the Recordset asynchronously by adding the value 32-adAsyncFetch. Whether or not you've specified an asynchronous option, an ExecuteComplete event is raised in your code when the Execute command completes.

You can cancel an asynchronous operation any time by issuing the Cancel method. This method doesn't take any arguments. You never need to specify which operation you want to cancel because only one asynchronous operation can be active on a given connection.

The BeginTrans, CommitTrans, and RollbackTrans methods let you control when a transaction begins and ends. You start a transaction by issuing a BeginTrans method:

level = cn.BeginTrans

This method returns the transaction level: 1 for top-level transactions that aren't nested in any other transactions, 2 for transactions nested in a top-level transaction, and so on. The BeginTrans, CommitTrans, and RollbackTrans methods all return an error if the provider doesn't support transactions. You can find out if the provider supports transactions by checking whether the Connection object exposes a custom property named Transaction DDL:

On Error Resume Next value = cn.Properties("Transaction DDL") If Err = 0 Then level = cn.BeginTrans If level = 1 Then MsgBox "A top-level transaction has been initiated" Else MsgBox "A nested transaction has been initiated" End If Else MsgBox "This provider doesn't support transactions" End If

The CommitTrans method commits the current transaction—that is, it makes all the changes in the database permanent. Conversely, the RollbackTrans method rolls back the current transaction, thus undoing all changes that the code performed while the transaction was active. You can be certain that a CommitTrans method permanently writes data to the database only if the transaction is a top-level transaction: In all other cases, the current transaction is nested in another transaction that could be rolled back.

The value of the Attributes property affects what happens when you commit or roll back a transaction. If the Attributes property has the adXactCommitRetaining bit set, the provider automatically starts a new transaction immediately after a CommitTrans method; if the Attributes property has the adXactAbortRetaining bit set, the provider starts a new transaction after every RollbackTrans method.

The only method I haven't described yet is OpenSchema. This method queries a data source and returns a Recordset that contains information on its structure (table names, field names, and so on). I don't expect that you'll use this method often, however, because ADO 2.1 specifications extend the ADO object model with items that let you get information about the structure of a data source using a less cryptic object-oriented approach, as I explain at the end of this chapter. If you do use this method, be aware of a bug: It doesn't work with server-side Recordsets, which unfortunately are the default in ADO. Therefore, if you use the OpenSchema method, remember to set the Connection's CursorLocation property to adUseClient before opening a Recordset.

The Connection object exposes nine events. Not all the events have the same syntax, but a few patterns do recur, and it makes more sense to describe the patterns than to examine each event individually.

Most ADO events are grouped in pairs. For example, the Connection object exposes the WillConnect and ConnectComplete events, which fire immediately before and immediately after a connection is established, respectively. Another pair, WillExecute and ExecuteComplete, lets you run code immediately before a command is executed on the connection and immediately after it has completed. The key to these Willxxxx and xxxxComplete events is the adStatus parameter.

On entry to a Willxxxx event, this parameter can be 1-adStatusOK (no errors), 2-adStatusErrorsOccurred (an error has occurred), or 3-adStatusCantDeny (no errors, and the operation can't be canceled). Your event procedure can modify the value of the adStatus parameter to 4-adStatusCancel if you want to cancel the operation or 5-adStatusUnwantedEvent if you don't want to receive the event from the ADO object any longer. You can't use the adStatusCancel value if the event procedure receives adStatus equal to adStatusCantDeny.

The same status values are used for xxxxComplete events, but in this case, the operation has already been completed so you can't set adStatus to adStatusCancel. Even if you cancel an operation in the Willxxxx event, the corresponding xxxxComplete event will fire, but it will receive the value adStatusCancel in adStatus. When you cancel an operation, the program receives error 3712, "Operation canceled by the user," even if you reset the Errors collection or the adStatus argument while inside the xxxxComplete event procedure.

You'll see that many ADO events receive in their last parameter a pointer to the object that's raising the event. This argument is never necessary in Visual Basic: Because you can trap only events coming from individual objects, you must already have a reference to the object itself. In other languages—for example, Microsoft Visual C++—you can write event procedures that trap events raised from multiple objects, in which case the object reference is necessary to figure out where the event comes from.

Let's take a quick look at the events of the Connection object. The WillConnect event fires when an Open method has been attempted on the connection. It receives the four arguments passed to the Open method plus the adStatus parameter and a pointer to the Connection object itself:

Private Sub cn_WillConnect(ConnectionString As String, UserID As String, _ Password As String, Options As Long, _ adStatus As ADODB.EventStatusEnum, _ ByVal pConnection As ADODB.Connection)

You can use this method to modify the connection string, the user ID, or the password on the fly. When a connect operation is completed—whether or not it succeeded—the Connection object raises a ConnectComplete event, which receives an Error object and the ubiquitous adStatus parameter:

Private Sub cn_ConnectComplete(ByVal pError As ADODB.error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pConnection As ADODB.Connection)

The Connection object also exposes the Disconnect event, which (obviously) fires when the connection is closed:

Private Sub cn_Disconnect(adStatus As ADODB.EventStatusEnum, _ pConnection As Connection)

Setting the adStatus parameter to adStatusUnwantedEvent has no effect on ConnectComplete and Disconnect events.

The WillExecute event fires before any command is attempted on the connection:

Private Sub cn_WillExecute(Source As String, _ CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, _ Options As Long, adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, _ ByVal pRecordset As ADODB.Recordset, _ ByVal pConnection As ADODB.Connection)

Source is an SQL string or the name of a stored procedure. CursorType identifies the type of cursor. (For more information about the CursorType property of the Recordset object, see the "Working with Cursors" section later in this chapter.) LockType is the type of lock to be enforced on the returned Recordset. (See the LockType property of the Recordset object.) Options corresponds to the argument with the same name that was passed to the Execute method. If the command won't return a Recordset, then CursorType and LockType parameters are set to -1. Because all these parameters are passed by reference, you can modify them if you want to. The last three arguments are pointers to the Connection, Command, and Recordset objects that are the source of the event. The pConnection parameter always points to the active Connection object. This event fires whenever a Connection's Execute method, a Command's Execute method, or a Recordset's Open method is attempted.

The ExecuteComplete event fires when the execution of a stored procedure or an SQL query comes to an end:

Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, _ ByVal pError As ADODB.error, adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _ ByVal pConnection As ADODB.Connection)

RecordsAffected is the number of records that have been affected by the operation (the same value that is returned in the Execute method's second argument). pError and adStatus have the usual meanings. The last three parameters are pointers to the objects that are raising the event.

The BeginTransComplete event fires when a BeginTrans method has completed its execution. The first parameter contains the value that is about to be returned to the program—that is, the level of the transaction just initiated. The meanings of all the other arguments should be self-explanatory.

Private Sub cn_BeginTransComplete(ByVal TransactionLevel As Long, _ ByVal pError As ADODB.error, adStatus As ADODB.EventStatusEnum, _ ByVal pConnection As ADODB.Connection)

The syntax of the CommitTransComplete and RollbackTransComplete events is similar to that of BeginTransComplete, but no information about the transaction level is passed to the event:

Private Sub cn_CommitTransComplete(ByVal pError As ADODB.error, adStatus _ As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) Private Sub cn_RollbackTransComplete(ByVal pError As ADODB.error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

The only other event exposed by the Connection object is InfoMessage. This event fires when the database engine sends a message or a warning or when a stored procedure executes a PRINT or RAISERROR SQL statement:

Private Sub cn_InfoMessage(ByVal pError As ADODB.error, adStatus As _ ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

In most cases, you'll want to test the pError parameter or examine the elements in the Errors collection to understand what exactly happened.

The Connection object exposes the Errors property, which returns a collection of all the errors that have occurred on the connection itself. More precisely, each time an error occurs, the Errors collection is cleared and then filled with the errors raised by all the layers that sit between your program and the data source, including the ODBC driver (if you're using the MSDASQL OLE DB Provider) and the database engine itself. You can examine all the items in this collection to find out where the error originated and how the layers have interpreted it. You won't find ADO errors—for example, the errors that occur when you pass an invalid value to an ADO property or method—in this collection, because those errors are considered to be regular Visual Basic errors and should be managed by a standard error-trapping handler.

Each Error object in the collection exposes several properties that let you understand what exactly went wrong. The Number, Description, HelpFile, and HelpContext properties have the same meanings as they do in the Visual Basic Error object. The Source property is especially important if you want to track down where the error occurred. The SQLState and NativeError properties return information about errors in SQL data sources. An ODBC source returns errors as defined in the ODBC 3 specifications.

ADO clears the Errors collection when the code executes the Clear method. ADO clears the Errors collection of the Connection object, however, only when an error actually occurs. For this reason, you might find it convenient to manually clear the collection before invoking methods of the Connection object that can potentially raise errors.